package org.stvd.repository.common.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.stvd.core.dto.QueryResult;
import org.stvd.core.jdbc.Pagination;
import org.stvd.entities.common.CodeTable;
import org.stvd.repository.common.CodeTableDao;

@Repository("CodeTableDao")
public class CodeTableDaoImpl implements CodeTableDao {

    @Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Override
    public int insert(String tableName, String id, String name, String pym, int pxh) {
        return this.jdbcTemplate.update("INSERT INTO " + tableName + "(id,name,pym,pxh) VALUES(?,?,?,?)", id, name, pym, pxh);
    }

    @Override
    public int update(String tableName, String id, String name, String pym, int pxh) {
        return this.jdbcTemplate.update("UPDATE " + tableName + " SET name=?,pxh=?,pym=? WHERE id=?", name, pxh, pym, id);
    }

    @Override
    public int delete(String tableName, String id) {
        return this.jdbcTemplate.update("DELETE FROM " + tableName + " WHERE id=?", id);
    }

    @Override
    public CodeTable findById(String tableName, String id) {
        return this.jdbcTemplate.queryForObject("SELECT id,name,pym,pxh FROM " + tableName + " WHERE id=?",
            new BeanPropertyRowMapper<CodeTable>(CodeTable.class), id);
    }

    @Override
    public QueryResult<CodeTable> getQueryResult(String tableName, int start, int limit, String id, String name, String pym) {
        QueryResult<CodeTable> queryResult = new QueryResult<CodeTable>();
        List<Object> params = new ArrayList<Object>();
        String sSQL = "SELECT id,name,pym,pxh FROM " + tableName + " WHERE 1=1 ";
        if (id != null && !id.equals("")) {
            sSQL += "AND id like ? ";
            params.add(id + "%");
        }
        if (name != null && !name.equals("")) {
            sSQL += "AND name like ? ";
            params.add("%" + name + "%");
        }
        if (pym != null && !pym.equals("")) {
            sSQL += "AND pym like ? ";
            params.add(pym + "%");
        }
        sSQL += "ORDER BY pxh ASC";

        Pagination<CodeTable> pagination = new Pagination<CodeTable>(sSQL, params.toArray(),
                (int) Math.floor(start * 1d / limit) + 1, limit, CodeTable.class,jdbcTemplate);
        queryResult.setPageCount(pagination.getTotalPages());
        queryResult.setPageIndex((int) Math.floor(start * 1d / limit) + 1);
        if (queryResult.getPageCount() > 0 && queryResult.getPageCount() < queryResult.getPageIndex()) {
            queryResult.setPageIndex(queryResult.getPageIndex() - 1);
        }
        queryResult.setTotal(pagination.getTotalRows());
        queryResult.setRows(pagination.getResultList());
        return queryResult;
    }

    @Override
    public List<CodeTable> findAll(String tableName, String id, String name, String pym) {
        List<Object> params = new ArrayList<Object>();
        String sSQL = "SELECT id,name,pym,pxh FROM " + tableName + " WHERE 1=1 ";
        if (id != null && !id.equals("")) {
            sSQL += "AND id like ? ";
            params.add(id + "%");
        }
        if (name != null && !name.equals("")) {
            sSQL += "AND name like ? ";
            params.add("%" + name + "%");
        }
        if (pym != null && !pym.equals("")) {
            sSQL += "AND pym like ? ";
            params.add(pym + "%");
        }
        sSQL += "ORDER BY pxh ASC";

        return this.jdbcTemplate.query(sSQL, params.toArray(), new BeanPropertyRowMapper<CodeTable>(CodeTable.class));
    }
    
    @Override
    public List<Map<String, Object>> listAllCodeTable(String tableName) {
        String sql = "SELECT TABLE_NAME table_name, TABLE_COMMENT table_comment, CREATE_TIME create_time, UPDATE_TIME modify_time"
                + " FROM information_schema.tables WHERE table_schema = (select database())"
                + " AND LOCATE('dic_',TABLE_NAME) > 0"
                + " AND TABLE_NAME LIKE ?"
                + " ORDER BY CREATE_TIME";
        return this.jdbcTemplate.queryForList(sql, "%"+tableName+"%");
    }

    @Override
    public Map<String, Object> getCodeTableByName(String tableName) {
        String sql = "SELECT TABLE_NAME table_name, TABLE_COMMENT table_comment"
            + " FROM information_schema.tables WHERE table_schema = (select database())"
            + " AND TABLE_NAME = '" + tableName +"'";
        List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
        if(list!=null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    @Override
    @Transactional
    public void insertCodeTable(String tableName, String tableComment) {
        String sql = "CREATE TABLE " + tableName
                +" (`id` varchar(6) NOT NULL COMMENT '主键ID',"
                +" `name` varchar(20) NOT NULL COMMENT '名称',"
                +" `pxh` decimal(1,0) NOT NULL COMMENT '排序号',"
                +" `pym` varchar(20) DEFAULT NULL COMMENT '拼音码',"
                +" PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT "
                +" CHARSET=utf8mb4 COMMENT = '" + tableComment +"'";
        this.jdbcTemplate.execute(sql);
    }

    @Override
    @Transactional
    public void updateCodeTable(String tableName, String tableComment) {
        String sql = "ALTER TABLE "+ tableName+" COMMENT '" + tableComment +"'";
        this.jdbcTemplate.execute(sql);
    }

    @Override
    @Transactional
    public void deleteCodeTable(String tableName) {
        this.jdbcTemplate.execute("DROP TABLE " + tableName);
    }
}
